module.exports = {
    SQL_QUERY_USER: function (username, password) {
        return `SELECT * FROM user WHERE username = '${username}' AND password = '${password}'`
    },
    SQL_USER_ID_COUNT: function () {
        return 'SELECT username FROM user'
    },
    SEL_SELECT_USER_ONE: function (username) {
      return `SELECT * FROM user WHERE username = ${username}`
    },
    SQL_ADD_USER_ONE: function (username, password) {
        return `INSERT INTO user (username, password) VALUES ('${username}', '${password}')`
    },
    SQL_SELECT_USER_ONG: function (username) {
        return `SELECT username FROM user WHERE username = ${username}`
    },
    SQL_UPDATE_USER_INFO: function (username, address, nikename, signature, cover, sex) {
        return `UPDATE user SET address = ${`'${address}'` || null}, sex = ${sex || null}, nikename = ${`'${nikename}'` || null}, signature = ${`'${signature}'` || null}, cover = ${`'${cover}'` || null} WHERE username = ${username}`
    },
    SQL_SELECT_NORMAL_ARTICLE: function (pageSize, currentPage) {
        return `SELECT * FROM article WHERE status = 'NORMAL' LIMIT ${pageSize} OFFSET ${(currentPage - 1) * pageSize}`
    },
    SQL_SELECT_ALL_ARTICLE: function (pageSize, currentPage) {
        return `SELECT * FROM article LIMIT ${pageSize} OFFSET ${(currentPage - 1) * pageSize}`
    },
    SQL_SELECT_NORMAL_ARTICLE_COUNT: function () {
        return `SELECT COUNT(*) FROM article WHERE status = 'NORMAL'`
    },
    SQL_SELECT_ALL_ARTICLE_COUNT: function () {
        return `SELECT COUNT(*) FROM article`
    },
    SQL_SELECT_ARTICLE_ONE: function (articleId, set) {
        return `SELECT * FROM article WHERE articleId = ${articleId} ${ set ? "AND status = 'NORMAL'" : ''}`
    },
    SQL_SELECT_ARTICLE_ONE_ALL: function (articleId, set) {
        return `SELECT * FROM article WHERE articleId = ${articleId}`
    },
    SQL_SELECT_ARTICLE_STATUS_ONE: function (articleId) {
        return `SELECT status FROM article WHERE articleId = ${articleId}`
    },
    SQL_SELECT_ARTICLE_AUTHOR_ID_ONE: function (username, articleId) {
        return `SELECT * FROM article WHERE authorId = ${username} AND articleId = ${articleId} AND status = 'NORMAL'`
    },
    SQL_UPDATE_ARTICLE_STATUS_ONE: function (articleId, status) {
        return `UPDATE article SET status = '${status}' WHERE articleId = ${articleId}`
    },
    SQL_UPDATE_ARTICLE_PREVIEW_TOTAL: function (previewTotal, articleId) {
        return `UPDATE article SET previewTotal = ${previewTotal} WHERE articleId = ${articleId}`
    },
    SQL_SELECT_ARTICLE_ONE_DETAIL: function (articleId) {
        return `SELECT * FROM article t1 LEFT JOIN article_content t2 ON  t2.id = t1.articleId WHERE t1.articleId = ${articleId}`
    },
    SQL_UPDATE_ARTICLE_ONE: function (articleId, articleTitle, status, description, type, updateTime, cover) {
        return `UPDATE article SET articleTitle = '${articleTitle}', status = '${status}', description = '${description}', type = ${type}, updateTime = '${updateTime}', cover = '${cover}' WHERE articleId = ${articleId}`
    },
    SQL_UPDATE_ARTICLE_CONTENT_ONE: function (articleId, content) {
        return `UPDATE article_content SET content = '${content}' WHERE id = ${articleId}`
    },
    SQL_SELECT_ARTICLE_ALL_ID: function () {
        return `SELECT articleId FROM article`
    },
    SQL_CREATE_ARTICLE_ONE: function (articleId, articleTitle, createTime, authorId, status, description, type, cover) {
        return `INSERT INTO article (articleId, articleTitle, createTime, authorId, status, description, type, cover) values (${articleId}, '${articleTitle}', '${createTime}', ${authorId}, '${status}', '${description}', ${type}, '${cover}')`
    },
    SQL_CREATE_ARTICLE_CONTENT_ONE: function (articleId, content) {
        return `INSERT INTO article_content (id, content) values (${articleId}, "${content}")`
    },
    SQL_DELETE_ARTICLE_ONE: function (username, articleId) {
        return `DELETE FROM article WHERE articleId = ${articleId} AND authorId = ${username}`
    },
    SQL_DELETE_ARTICLE_CONTENT_ONE: function (articleId) {
        return `DELETE FROM article_content WHERE id = ${articleId}`
    },
    SQL_UPDATE_USER_COLLECTIONS_ID: function (articleId) {
        return `UPDATE user SET collections = REPLACE(collections, '${articleId}', '') WHERE collections LIKE '%${articleId}%'`
    },
    SQL_UPDATE_USER_COLLECTIONS_OTHER: function (articleId) {
        return `UPDATE user SET collections = REPLACE(collections, ',', '') WHERE collections LIKE '%,%'`
    },
    SQL_SELECT_MY_ARTICLE_LIST: function (username, pageSize, currentPage) {
        return `SELECT * FROM article WHERE authorId = ${username} AND status = 'NORMAL' LIMIT ${pageSize} OFFSET ${(currentPage - 1) * pageSize}`
    },
    SQL_SELECT_MY_ARTICLE_LIST_COUNT: function (username) {
        return `SELECT COUNT(*) FROM article WHERE authorId = ${username} AND status = 'NORMAL'`
    },
    SQL_UPDATE_USER_COLLECT: function (username, collections) {
        return `UPDATE user SET collections = '[${collections}]' WHERE username = ${username}`
    },
    SQL_UPDATE_ARTICLE_COLLECT: function (articleId, total) {
        return `UPDATE article SET collectionTotal = ${total} WHERE articleId = ${articleId}`
    },
    SQL_UPDATE_USER_SUB: function (username, subs, total) {
        return `UPDATE user SET subs = '[${subs}]', subTotal = ${total} WHERE username = ${username}`
    },
    SQL_UPDATE_USER_FANS: function (username, fans, total) {
        return `UPDATE user SET fans = '[${fans}]', fansTotal = ${total} WHERE username = ${username}`
    }
}